USE cfExam;

-- For M$ SQL SERVER
CREATE TABLE [dbo].[answer](
    [answer_id] [int] IDENTITY(1,1) NOT NULL,
    [answer] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [question_id] [int] NULL,
    [is_correct] [bit] NULL,
 CONSTRAINT [PK_answer] PRIMARY KEY CLUSTERED 
(
    [answer_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[attempt](
    [attempt_id] [int] IDENTITY(1,1) NOT NULL,
    [is_finish] [bit] NULL,
    [final_mark] [float] NULL,
    [user_id] [int] NOT NULL,
 CONSTRAINT [PK_attempt] PRIMARY KEY CLUSTERED 
(
    [attempt_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[attempt_question](
    [attempt_id] [int] NOT NULL,
    [question_id] [int] NOT NULL,
    [user_answer_ids] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [is_correct] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[exam](
    [exam_id] [int] IDENTITY(1,1) NOT NULL,
    [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_exam] PRIMARY KEY CLUSTERED 
(
    [exam_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[question](
    [question_id] [int] IDENTITY(1,1) NOT NULL,
    [question] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [exam_id] [int] NOT NULL,
 CONSTRAINT [PK_question] PRIMARY KEY CLUSTERED 
(
    [question_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[user_exam](
    [user_id] [int] NULL,
    [exam_id] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[users](
    [user_id] [int] IDENTITY(1,1) NOT NULL,
    [username] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [password] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
    [user_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[answer]  WITH CHECK ADD  CONSTRAINT [FK_answer_question] FOREIGN KEY([question_id])
REFERENCES [dbo].[question] ([question_id])
GO

ALTER TABLE [dbo].[attempt]  WITH CHECK ADD  CONSTRAINT [FK_attempt_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO

ALTER TABLE [dbo].[attempt_question]  WITH CHECK ADD  CONSTRAINT [FK_attempt_question_attempt] FOREIGN KEY([attempt_id])
REFERENCES [dbo].[attempt] ([attempt_id])
GO

ALTER TABLE [dbo].[attempt_question]  WITH CHECK ADD  CONSTRAINT [FK_attempt_question_question] FOREIGN KEY([question_id])
REFERENCES [dbo].[question] ([question_id])
GO

ALTER TABLE [dbo].[question]  WITH CHECK ADD  CONSTRAINT [FK_question_exam] FOREIGN KEY([exam_id])
REFERENCES [dbo].[exam] ([exam_id])
GO

ALTER TABLE [dbo].[user_exam]  WITH CHECK ADD  CONSTRAINT [FK_user_exam_exam] FOREIGN KEY([exam_id])
REFERENCES [dbo].[exam] ([exam_id])
GO

ALTER TABLE [dbo].[user_exam]  WITH CHECK ADD  CONSTRAINT [FK_user_exam_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO


